﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using AlarmManagePlatform.Model.DB;
using System.Data;

namespace AlarmManagePlatform.DataAccess
{
    internal class tb_SubmitUnit
    {
        internal void updateGPS(string Name, float latitude, float longitude, string desc, string uploadDate)
        {
            Relations.DBUtility.SqlServer.SqlDb sqlDb = null;
            try
            {
                string sqlScript = string.Format("update tb_SubmitUnit set GPSInfo = '{0}',uploadDate = '{2}' where Name = '{1}';", latitude.ToString() + "|" + longitude.ToString() + "|" + desc, Name, uploadDate);
                sqlDb = Static.Methods.OpenDataBase();
                sqlDb.UpdateSqlScript(sqlScript);
            }
            catch (Exception ex)
            {
                throw new Exception("更新送检单位GPS信息失败，错误信息：" + ex.Message);
            }
            finally
            {
                if (sqlDb != null)
                    Static.Methods.CloseDataBase(sqlDb);
            }
        }

        internal bool CheckSubmitUnitIsExist(string name)
        {
            Relations.DBUtility.SqlServer.SqlDb sqlDb = null;
            try
            {
                string sqlScript;
                sqlScript = string.Format("Select Id From tb_SubmitUnit Where Name = '{0}';", name);
                sqlDb = Static.Methods.OpenDataBase();
                if (sqlDb.RunSqlScript(sqlScript).Rows.Count > 0)
                    return true;
                else
                    return false;
            }
            catch (Exception ex)
            {
                throw new Exception("检查送检单位是否存在失败，错误信息：" + ex.Message);
            }
            finally
            {
                if (sqlDb != null)
                    Static.Methods.CloseDataBase(sqlDb);
            }
        }


        internal void UpdateSubmitUnit(Model.DB.tb_SubmitUnit submitUnit)
        {
            Relations.DBUtility.SqlServer.SqlDb _SqlDb = null;
            string _SqlScript = string.Empty;
            try
            {
                _SqlScript += $"  Update tb_SubmitUnit Set [Name] = '{submitUnit.Name}',[Latitude] = '{submitUnit.Latitude}',[Longitude] = '{submitUnit.Longitude}',[GPSDesc] = '{submitUnit.GPSDesc}',[uploadDate] = '{DateTime.Now.ToString(Static.ParaInfo.DateFormatLong)}'";
                _SqlScript += $"  Where Id = {submitUnit.Id}";

                _SqlDb = Static.Methods.OpenDataBase();
                _SqlDb.UpdateSqlScript(_SqlScript);
            }
            catch (Exception ex)
            {
                throw new Exception("修改送检单位失败，错误信息：" + ex.Message);
            }
            finally
            {
                if (_SqlDb != null)
                    Static.Methods.CloseDataBase(_SqlDb);
            }
        }


        internal void SaveSubmitUnit(Model.DB.tb_SubmitUnit submitUnit)
        {
            Relations.DBUtility.SqlServer.SqlDb _SqlDb = null;
            string _SqlScript = string.Empty;
            try
            {
                _SqlScript += "Insert into tb_SubmitUnit ([Name],[Latitude],[Longitude],[GPSDesc],[uploadDate])";
                _SqlScript += $"Values ('{submitUnit.Name}','{submitUnit.Latitude}','{submitUnit.Longitude}','{submitUnit.GPSDesc}','{DateTime.Now.ToString(Static.ParaInfo.DateFormatLong)}')";


                _SqlDb = Static.Methods.OpenDataBase();
                _SqlDb.UpdateSqlScript(_SqlScript);
            }
            catch (Exception ex)
            {
                throw new Exception("保存送检单位失败，错误信息：" + ex.Message);
            }
            finally
            {
                if (_SqlDb != null)
                    Static.Methods.CloseDataBase(_SqlDb);
            }
        }

        internal int GetSubmitUnitCount(Dictionary<string,string> logisticList)
        {
            Relations.DBUtility.SqlServer.SqlDb _SqlDb = null;
            int _Result;
            string _Scalar = string.Empty;
            string _SqlScript;
            try
            {
                _Result = -1;

                _SqlScript = $"    Select Count(*) From tb_SubmitUnit  ";
                if (!string.IsNullOrEmpty( logisticList["Name"]))
                {
                    _SqlScript += $"    Where  Name Like '%{logisticList["Name"]}%'";
                }
                _SqlDb = Static.Methods.OpenDataBase();

                _Scalar = _SqlDb.RunSqlScriptReturnScalar(_SqlScript);

                if (string.IsNullOrEmpty(_Scalar))
                {
                    _Result = 0;
                }
                else
                {
                    _Result = Convert.ToInt32(_Scalar);
                }

                return _Result;
            }
            catch (Exception ex)
            {
                throw new Exception("获取送检单位数量失败，错误信息：" + ex.Message);
            }
            finally
            {
                if (_SqlDb != null)
                    Static.Methods.CloseDataBase(_SqlDb);
            }
        }

        internal string GetSubmitUnitId(string name)
        {
            Relations.DBUtility.SqlServer.SqlDb _SqlDb = null;
            string _Result = string.Empty;
            string _SqlScript;
            try
            {
                _SqlScript = $"Select Id From tb_SubmitUnit Where Name = '{name}'";
                _SqlDb = Static.Methods.OpenDataBase();

                _Result = _SqlDb.RunSqlScriptReturnScalar(_SqlScript);

                return _Result;
            }
            catch (Exception ex)
            {
                throw new Exception("获取送检单位ID失败，错误信息：" + ex.Message);
            }
            finally
            {
                if (_SqlDb != null)
                    Static.Methods.CloseDataBase(_SqlDb);
            }
        }

        internal void DeleteForID(List<string> indexList)
        {
            Relations.DBUtility.SqlServer.SqlDb _SqlDb = null;
            string _TaskIds = string.Empty;
            string _SqlScript = string.Empty;
            try
            {
                _SqlDb = Static.Methods.OpenDataBase();
                _TaskIds = Static.Methods.CombiningStrings2<string>(indexList);

                _SqlScript = "Delete From tb_SubmitUnit";
                _SqlScript += $" Where Id in ( {_TaskIds} )";

                _SqlDb.UpdateSqlScript(_SqlScript);
            }
            catch (Exception ex)
            {
                throw new Exception("删除送检单位失败，错误信息：" + ex.Message);
            }
            finally
            {
                if (_SqlDb != null)
                    Static.Methods.CloseDataBase(_SqlDb);
            }
        }

        internal Model.DB.tb_SubmitUnit GetSubmitUnitForId(string id)
        {
            Relations.DBUtility.SqlServer.SqlDb _SqlDb = null;
            List<Model.DB.tb_SubmitUnit> _List;
            DataTable _DataTable;
            string _SqlScript;
            try
            {
                _List = new List<Model.DB.tb_SubmitUnit>();

                _SqlScript = $"Select * From tb_SubmitUnit Where Id = {id} Order  By Id";
                _SqlDb = Static.Methods.OpenDataBase();
                _DataTable = _SqlDb.RunSqlScript(_SqlScript);

                _List = DataTableToList(_DataTable);
                if (_List.Count > 0)
                    return _List[0];
                else
                    return null;


            }
            catch (Exception ex)
            {
                throw new Exception("获取送检单位信息失败，错误信息：" + ex.Message);
            }
            finally
            {
                if (_SqlDb != null)
                    Static.Methods.CloseDataBase(_SqlDb);
            }
        }

        internal List<Model.DB.tb_SubmitUnit> GetSubmitUnitList()
        {
            Relations.DBUtility.SqlServer.SqlDb _SqlDb = null;
            List<Model.DB.tb_SubmitUnit> _Result;
            DataTable _DataTable;
            string _SqlScript;
            try
            {
                _Result = new List<Model.DB.tb_SubmitUnit>();

                _SqlScript = "Select * From tb_SubmitUnit Order By Name";
                _SqlDb = Static.Methods.OpenDataBase();
                _DataTable = _SqlDb.RunSqlScript(_SqlScript);

                _Result = DataTableToList(_DataTable);
                return _Result;
            }
            catch (Exception ex)
            {
                throw new Exception("获取送检单位信息失败，错误信息：" + ex.Message);
            }
            finally
            {
                if (_SqlDb != null)
                    Static.Methods.CloseDataBase(_SqlDb);
            }
        }

        internal List<Model.DB.tb_SubmitUnit> GetSubmitUnitList(int startIndex, int searchCount, Dictionary<string, string> logisticList)
        {
            Relations.DBUtility.SqlServer.SqlDb _SqlDb = null;
            List<Model.DB.tb_SubmitUnit> _Result;
            DataTable _DataTable;
            string _SqlScript;
            try
            {
                _Result = new List<Model.DB.tb_SubmitUnit>();

                //_SqlScript = $"Select Top {searchCount} * From tb_SubmitUnit ";
                //_SqlScript += $"    Where Id Not In (  Select Top {startIndex} Id From  tb_SubmitUnit ";
                //if (!string.IsNullOrEmpty(logisticList["Name"]))
                //{
                //    _SqlScript += $"    Where  Name Like '*{logisticList["Name"]}*'";
                //}
                //_SqlScript += "     Order By Id )";

                //if (!string.IsNullOrEmpty(logisticList["Name"]))
                //{
                //    _SqlScript += $"    Where  Name Like '*{logisticList["Name"]}*'";
                //}

                //_SqlScript += "     Order By Id";

                _SqlScript = "      SELECT * FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY [Name] ASC) AS Num FROM tb_SubmitUnit ";

                if (!string.IsNullOrEmpty(logisticList["Name"]))
                {
                    _SqlScript += $"    Where  [Name] like  '%{logisticList["Name"]}%'";
                }

                _SqlScript += $"    )AS TempTable";
                _SqlScript += $"    WHERE Num BETWEEN {startIndex} AND {startIndex + searchCount - 1}"; 

                _SqlDb = Static.Methods.OpenDataBase();
                _DataTable = _SqlDb.RunSqlScript(_SqlScript);

                _Result = DataTableToList(_DataTable);

                return _Result;
            }
            catch (Exception ex)
            {
                throw new Exception("获取送检单位信息失败，错误信息：" + ex.Message);
            }
            finally
            {
                if (_SqlDb != null)
                    Static.Methods.CloseDataBase(_SqlDb);
            }
        }
        internal List<Model.DB.submitUnitGPSInfo> getSubmitUnitGPSInfo()
        {
            Relations.DBUtility.SqlServer.SqlDb _SqlDb = null;
            List<Model.DB.submitUnitGPSInfo> _Result;
            Model.DB.submitUnitGPSInfo _tb_SubmitUnit = null;
            DataTable _DataTable;
            string _SqlScript, stringTemp;
            try
            {
                _Result = new List<Model.DB.submitUnitGPSInfo>();
                _SqlScript = "Select id,name,Latitude,Longitude,GPSDesc,uploadDate From tb_SubmitUnit Order By Name";
                _SqlDb = Static.Methods.OpenDataBase();
                _DataTable = _SqlDb.RunSqlScript(_SqlScript);
                for (int i = 0; i < _DataTable.Rows.Count; i++)
                {
                    if (!Relations.Common.Methods.IsFloat(_DataTable.Rows[i]["Latitude"].ToString()) || !Relations.Common.Methods.IsFloat(_DataTable.Rows[i]["Longitude"].ToString())) continue;

                    _tb_SubmitUnit = new Model.DB.submitUnitGPSInfo();
                    _tb_SubmitUnit.id = int.Parse(_DataTable.Rows[i]["Id"].ToString());
                    _tb_SubmitUnit.name = _DataTable.Rows[i]["Name"].ToString();
                    _tb_SubmitUnit.latitude = float.Parse(_DataTable.Rows[i]["Latitude"].ToString());
                    _tb_SubmitUnit.longitude = float.Parse(_DataTable.Rows[i]["Longitude"].ToString());
                    _tb_SubmitUnit.GpsDesc = _DataTable.Rows[i]["GPSDesc"].ToString();


                    if (!string.IsNullOrEmpty(_DataTable.Rows[i]["uploadDate"].ToString()))
                        _tb_SubmitUnit.uploadDate = DateTime.Parse(_DataTable.Rows[i]["uploadDate"].ToString()).ToString(Static.ParaInfo.DateFormatLong);


                    _Result.Add(_tb_SubmitUnit);
                }
                return _Result;
            }
            catch (Exception ex)
            {
                throw new Exception("获取送检单位信息失败，错误信息：" + ex.Message);
            }
            finally
            {
                if (_SqlDb != null)
                    Static.Methods.CloseDataBase(_SqlDb);
            }
        }

        internal List<GPSInfo> GetGPSInfo()
        {
            Relations.DBUtility.SqlServer.SqlDb _SqlDb = null;
            List<Model.DB.GPSInfo> _Result;
            Model.DB.GPSInfo _Temp = null;
            DataTable _DataTable;
            string _SqlScript;
            try
            {
                _Result = new List<GPSInfo>();

                _SqlScript = "Select id,name,Latitude,Longitude,GPSDesc,uploadDate From tb_SubmitUnit Order By Name";

                _SqlDb = Static.Methods.OpenDataBase();

                _DataTable = _SqlDb.RunSqlScript(_SqlScript);
                for (int i = 0; i < _DataTable.Rows.Count; i++)
                {
                    if (!Relations.Common.Methods.IsFloat(_DataTable.Rows[i]["Latitude"].ToString()) || !Relations.Common.Methods.IsFloat(_DataTable.Rows[i]["Longitude"].ToString())) continue;

                    _Temp = new Model.DB.GPSInfo();
                    _Temp.Id = _DataTable.Rows[i]["Id"].ToString();
                    _Temp.Name = _DataTable.Rows[i]["Name"].ToString();
                    _Temp.Latitude = float.Parse(_DataTable.Rows[i]["Latitude"].ToString());
                    _Temp.Longitude = float.Parse(_DataTable.Rows[i]["Longitude"].ToString());
                    _Temp.GpsDesc = _DataTable.Rows[i]["GPSDesc"].ToString();

                    if (!string.IsNullOrEmpty(_DataTable.Rows[i]["uploadDate"].ToString()))
                        _Temp.UploadDate = DateTime.Parse(_DataTable.Rows[i]["uploadDate"].ToString()).ToString(Static.ParaInfo.DateFormatLong);

                    _Result.Add(_Temp);
                }
                return _Result;
            }
            catch (Exception ex)
            {
                throw new Exception("获取送检单位GPS信息失败，错误信息：" + ex.Message);
            }
            finally
            {
                if (_SqlDb != null)
                    Static.Methods.CloseDataBase(_SqlDb);
            }
        }

        private List<Model.DB.tb_SubmitUnit> DataTableToList(DataTable dataTable)
        {
            List<Model.DB.tb_SubmitUnit> _List;
            Model.DB.tb_SubmitUnit _SubmitUnit;

            try
            {
                _List = new List<Model.DB.tb_SubmitUnit>();

                for (int i = 0; i < dataTable.Rows.Count; i++)
                {
                    _SubmitUnit = null;
                    _SubmitUnit = new Model.DB.tb_SubmitUnit();
                    _SubmitUnit.Id = Convert.ToInt32(dataTable.Rows[i]["Id"].ToString());
                    _SubmitUnit.Name = dataTable.Rows[i]["Name"].ToString();
                    _SubmitUnit.ImageInfo = dataTable.Rows[i]["ImageInfo"].ToString();
                    _SubmitUnit.GPSDesc = dataTable.Rows[i]["GPSDesc"].ToString();
                    _SubmitUnit.Latitude = dataTable.Rows[i]["Latitude"].ToString();
                    _SubmitUnit.Longitude = dataTable.Rows[i]["Longitude"].ToString();

                    //_SubmitUnit.uploadDate = DateTime.Parse(dataTable.Rows[i]["uploadDate"].ToString());
                    if (!string.IsNullOrEmpty(dataTable.Rows[i]["uploadDate"].ToString()))
                        _SubmitUnit.uploadDate = DateTime.Parse(dataTable.Rows[i]["uploadDate"].ToString()).ToString(Static.ParaInfo.DateFormatLong);


                    _List.Add(_SubmitUnit);
                }

                return _List;
            }
            catch (Exception ex)
            {
                throw new Exception("将Datatable转换成List<Model.DB.tb_SubmitUnit>有异常：" + ex.ToString());
            }
        }
    }
}